#!/usr/bin/python3
# -*- coding:utf-8 -*-
# __author__ = '__Tony__'

import orjson
import re


from log import log
from fastapi import APIRouter, Form, Body, File, UploadFile, Depends, Query
from fastapi.responses import JSONResponse, Response, StreamingResponse
from database import get_db
from utility.utility import download_zip, zip_files, download_file_stream, json_result_reps, result, is_number
from app.yg_crm.services import One2Many
from sqlalchemy.orm import Session
from app.yg_crm import crud, orm, schemas
import pandas as pd
from os import path
from app.oss_api.services import OssBaseHandler
from io import BytesIO
from typing import List
from datetime import datetime
from app.yg_crm.services.excelHandler import export_excel

yg_router = APIRouter()


@yg_router.post("/excel_one2many")
async def excel_one2many(file: UploadFile = File(alias='file'), styleTpl: str = Form(default='')):
    source_file, content_type = file.filename, file.content_type
    source_file_contents = await file.read()
    filesize = len(source_file_contents)
    # 文件名处理
    now = datetime.now()
    timestamp = now.strftime("%Y%m%d%H%M%S")
    original_excel_filename = path.basename(source_file)
    new_filename = "{0}-{1}".format(path.splitext(original_excel_filename)[0], timestamp)
    log.info(original_excel_filename, original_excel_filename)
    # 模板处理处理
    tpl_data = None
    json_params = {}
    if styleTpl:
        oss = OssBaseHandler(bucket_name="waybill")
        obj = oss.getObject(filepath=styleTpl)
        tpl_data = BytesIO(obj['data'])
        json_params = oss.is_json(obj['config'])
    # panda系列生成文件，压缩文件
    df = pd.read_excel(source_file_contents)
    # NaN值处理, NaN会造空值无法判断，数据无法处理
    df.fillna('', inplace=True)
    # df.dropna(inplace=True)
    # print(df)
    # df.columns
    biz_params = {'feeRate': 30.97}
    if json_params:
        biz_params.update(json_params)
        log.info(biz_params)
    writer1 = One2Many(df=df, sheet_name='主单', biz_params=biz_params)
    # writer1.save(tpl_file='./data/waybill_489-60197211_waybill_new.xlsx')
    files_writers = writer1.save(tpl_data) if tpl_data else writer1.save()
    zip_content = await zip_files(files=files_writers)
    # 返回ZIP文件作为响应
    log.info("finally...")
    return download_zip(zip_content, f'{new_filename}.zip')
    # return download_file_stream(zip_content, f'{new_filename}.zip')


"""
示例
source_file /Users/tonywu/Public/Demo/test_panda/data/bill_datasource.xlsx
oss: templates/waybill_template_01.xlsx
"""


@yg_router.get("/template_type")
async def get_template_type(db: Session = Depends(get_db)):
    res = crud.get_template_types(db)
    [db.refresh(r) for r in res]
    return json_result_reps(data=res)


@yg_router.get("/template")
async def get_template(db: Session = Depends(get_db)):
    res = crud.get_templates(db)
    [db.refresh(r) for r in res]
    return json_result_reps(data=res)


@yg_router.post("/template")
async def create_template(db: Session = Depends(get_db), template: schemas.Template = Body()):
    # print(template)
    res = crud.create_template(db, templates=[template])
    return json_result_reps(data=res)


@yg_router.get("/get_template_by_type")
async def get_template_by_type(db: Session = Depends(get_db), template_type_id: int = Query()):
    res = db.query(orm.Template).where(orm.Template.template_type_id == template_type_id).all()
    [db.refresh(r) for r in res]
    return json_result_reps(data=res)


@yg_router.get("/get_template_by_category")
async def get_template_by_type(db: Session = Depends(get_db), category: str = Query()):
    q = db.query(orm.Template).filter(orm.TemplateType.category == category)
    print(q)
    res = q.all()
    [db.refresh(r) for r in res]
    return json_result_reps(data=res)


@yg_router.delete("/template")
async def delete_template(db: Session = Depends(get_db), ids: List[int] = Body(alias="id", embed=True)):
    res = crud.get_templates_by_ids(db, ids=ids, is_delete=True)
    return json_result_reps(data=res)


@yg_router.post("/exportXlsBySql")
async def exportXls(db: Session = Depends(get_db), req_data: schemas.excelBySqlData = Body()):
    data = crud.getDataBySql(db, req_data.sql)

    print(req_data.id_template)
    tpl = crud.get_templates_by_ids(db, ids=[req_data.id_template])
    config_dict = orjson.loads(tpl[0].config.encode())
    # print(config_dict)
    subtitle = req_data.subtitle
    columns = []
    if config_dict:
        if 'columns' in config_dict:
            columns_tmp_dict = {c.dataIndex: c.__dict__ for c in req_data.columns if c.dataIndex in config_dict['columns']}
            for k, v in config_dict['columns'].items():
                if k in columns_tmp_dict:
                    c = columns_tmp_dict[k]
                    c.update(v)
                else:
                    c = v
                    c.update({"dataIndex": k})
                columns.append(c)
            log.info(columns)
        if 'subtitle' in config_dict and config_dict['subtitle'] == 'date':
            subtitle = f'日期：{datetime.now().strftime("%Y-%m-%d")}'
    else:
        columns = [c.__dict__ for c in req_data.columns]

    return export_excel(columns, data, req_data.fileName, req_data.sheetName, req_data.title, subtitle)


@yg_router.post("/stringHandler/specifications")
async def string_handler(param: schemas.stringData = Body()):
    # print(param)
    lst = param.inputData.split("\n")
    seperator = param.seperator or "\n"
    # print(lst)
    res = ""
    if param.chunkSize == 0:
        res = " ".join(lst)
    else:
        res_arr = []
        grp = [lst[i:i + param.chunkSize] for i in range(0, len(lst), param.chunkSize)]
        for g in grp:
            res_arr.append(" ".join(g))
        res = seperator.join(res_arr)
    return json_result_reps(data=res)


@yg_router.post("/stringHandler/specsToVolume")
async def string_handler(param: schemas.stringData = Body()):
    log.info(param)
    seperator = param.seperator or "\n"
    lst = re.split(f'[\n${seperator}]+', param.inputData)
    print(lst)
    specs = []
    total = 0
    for item in lst:
        if item.strip() == "":
            continue
        spec = {'str': item}
        match = re.match(r'^([0-9]+\.?[0-9]*)[\*]([0-9]+\.?[0-9]*)[\*]([0-9]+\.?[0-9]*)[\*]([0-9]+\.?[0-9]*)$', item.strip())
        try:
            if match:
                B = match.group(1)
                C = match.group(2)
                D = match.group(3)
                F = match.group(4)
                log.info("B:{}", B)
                log.info("C:{}", C)
                log.info("D:{}", D)
                log.info("F:{}", F)
                spec['result'] = (int(float(B)) * int(float(C)) * int(float(D))) / 1000000 * int(float(F))
                # 有一列错误，但后续不再计算总和，但是单列继续计算，以方便分析
                if total != "无法计算结果":
                    total += spec['result']
            else:
                spec['result'] = "规格不规范不能解析"
                total = "无法计算结果"
        except Exception as e:
            log.error(e)
            spec['result'] = "规格不规范不能解析"
            total = "无法计算结果"
        specs.append(f'{spec["str"]} = {spec["result"]}')
        total = round(total, 2) if is_number(str(total)) else total
    specs.append(f'总体积 = {total}')
    res = {'process': "\n".join(specs), 'total': total}
    return json_result_reps(data=res)


"""
全量字段：
{
  "bill_date": {
    "title": "帐单日期",
    "width": 100
  },
  "bill_no": {
    "title": "帐单号",
    "width": 120
  },
  "flight_no": {
    "title": "航班号",
    "width": 70
  },
  "departure_port": {
    "title": "始发港",
    "width": 60
  },
  "destination_port": {
    "title": "目的港",
    "width": 60
  },
  "articles": {
    "title": "件数",
    "width": 70
  },
  "weight": {
    "title": "重量",
    "width": 70
  },
  "weight_for_fee": {
    "title": "计费重量",
    "width": 70
  },
  "unit_price": {
    "title": "单价",
    "width": 60
  },
  "freight": {
    "title": "运费",
    "width": 65
  },
  "fee_bill_making": {
    "title": "制单费",
    "width": 65
  },
  "fee_customs_declaration": {
    "title": "报关费",
    "width": 65
  },
  "total_amount": {
    "title": "总金额",
    "width": 75
  },
  "status": {
    "title": "状态",
    "width": 60
  },
  "memo": {
    "title": "备注",
    "width": 120
  }
}
——>
{ 
"columns": {
  "xuhao": {
    "title": "序号",
    "width": 50
  },
  "bill_date": {
    "title": "日期"
  },
  "bill_no": {
    "title": "主单号"
  },
  "articles": {
    "title": "件数",
    "align": "center,vcenter"
  },
  "weight": {
    "title": "重量"
  },
  "weight_for_fee": {
    "title": "计费重量"
  },
  "destination_port": {
    "title": "国家地区"
  },
  "unit_price": {
    "title": "单价"
  },
  "freight": {
    "title": "运费"
  },
  "fee_bill_making": {
    "title": "制单费"
  },
  "fee_customs_declaration": {
    "title": "报关费"
  },
  "total_amount": {
    "title": "总金额"
  },
  "memo": {
    "title": "备注"
  }
},
"subtitle": "date"
}
"""
